package org.chenbingkang.jxl;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.*;

import java.io.File;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

/**
 * 导出EXCEL
 *
 */
public class OutPutExcel {

	public static final String FONT_NAME="宋体";

	public static void main(String ... args)throws Exception{
		String companyName="北京京东尚科信息技术有限公司";
		I18nBillDetailReportModel model=new I18nBillDetailReportModel();
		model.setAccount("test");
		model.setAccountName("测试");
		model.setCountryCode("86");
		model.setCountryName("中国");
		model.setEnterpriseCode("111");
		model.setEnterpriseName("京东云");
		model.setlMoney(12000L);
		model.setMoney("12,000.2300");
		model.setlPrice(12L);
		model.setPrice("0.0120");
		model.setlSuccess(10000L);
		model.setSuccess("10000");
		model.setSendDay("2020-05-03");
		createEnterpriseI18nExcel(companyName, Arrays.asList(model,model),Collections.singletonList(model),"12,000.03");
	}

	public static void createEnterpriseI18nExcel(String companyName,List<I18nBillDetailReportModel> summaryModels,List<I18nBillDetailReportModel> detailModels,String sumMoney)throws Exception{
		WritableWorkbook wwb = Workbook.createWorkbook(new File("/Users/chenbingkang/Downloads/test.xls"));
		WritableSheet summarySheet = wwb.createSheet("结算单", 0);
		createEnterpriseI18nDetailSummarySheet(summarySheet,companyName,summaryModels,sumMoney);
		WritableSheet detailSheet = wwb.createSheet("国家明细", 1);
		createEnterpriseI18nDetailSheet(detailSheet,detailModels);
		wwb.write();
		wwb.close();
	}

	public static void createEnterpriseI18nDetailSummarySheet(WritableSheet ws,String companyName, List<I18nBillDetailReportModel> models,String sumMoney)throws WriteException{
		int columnWidth=15;
		int rowHeight=500;
		for(int i=0;i<=10;i++){
			ws.setColumnView(i,columnWidth);
		}

		ws.setRowView(0,800);
		Label companyNameLabel =  new Label( 0 , 0 , companyName,getSummarySheetCompanyNameFormat() );
		ws.addCell(companyNameLabel);
		ws.mergeCells(0, 0, 10, 0);
		ws.addCell(new Label( 0 , 2, "结算单",getSummarySheetTextFormat(Border.NONE,12)));
		ws.mergeCells(0, 2, 10, 2);
		ws.addCell(new Label( 0 , 4, "客户名称：",getSummarySheetTextFormat()));

		ws.addCell(new Label( 1 , 4, models.get(0).getEnterpriseName(),getSummarySheetTextFormat()));
		ws.mergeCells(1, 4, 5, 4);
		ws.addCell(new Label( 6 , 4, "制表时间：",getSummarySheetTextFormat()));
		ws.mergeCells(6, 4, 7, 4);

		String today=LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy/MM/dd"));
		ws.addCell(new Label( 8 , 4, today,getSummarySheetTextFormat()));
		ws.mergeCells(8, 4, 10, 4);

		ws.addCell(new Label( 0 , 5, "详细说明如下：",getSummarySheetTextFormat(Border.NONE,10,Alignment.LEFT)));
		ws.mergeCells(0, 5, 10, 5);

		ws.addCell(new Label( 0 , 6, "服务名称",getSummarySheetTextFormat()));
		ws.addCell(new Label( 1 , 6, "服务目标客户",getSummarySheetTextFormat()));
		ws.addCell(new Label( 2 , 6, "内容：",getSummarySheetTextFormat()));
		ws.addCell(new Label( 3 , 6, "月份",getSummarySheetTextFormat()));
		ws.addCell(new Label( 4 , 6, "账号",getSummarySheetTextFormat()));
		ws.addCell(new Label( 5 , 6, "国家代码",getSummarySheetTextFormat()));
		ws.addCell(new Label( 6 , 6, "国家",getSummarySheetTextFormat()));
		ws.addCell(new Label( 7 , 6, "单价（元）",getSummarySheetTextFormat()));
		ws.addCell(new Label( 8 , 6, "计费总条数",getSummarySheetTextFormat()));
		ws.addCell(new Label( 9 , 6, "金额（元）",getSummarySheetTextFormat()));
		ws.addCell(new Label( 10 , 6, "总金额（元）",getSummarySheetTextFormat()));

		ws.addCell(new Label( 0 , 7, "集团短信业务协议",getSummarySheetTextFormat()));
		ws.addCell(new Label( 1 , 7, models.get(0).getEnterpriseName(),getSummarySheetTextFormat()));
		ws.addCell(new Label( 2 , 7, "*电信业务*信息服务费",getSummarySheetTextFormat()));
		ws.addCell(new Label( 3 , 7, models.get(0).getSendDay().substring(5,7)+"月",getSummarySheetTextFormat()));
		ws.addCell(new Label( 10 , 7, sumMoney,getSummarySheetTextFormat()));

		int rowIndex=6;
		for(I18nBillDetailReportModel model:models){
			rowIndex++;
			ws.addCell(new Label( 4 , rowIndex, model.getAccount(),getSummarySheetTextFormat()));
			ws.addCell(new Label( 5 , rowIndex, model.getCountryCode(),getSummarySheetTextFormat()));
			ws.addCell(new Label( 6 , rowIndex, model.getCountryName(),getSummarySheetTextFormat()));
			ws.addCell(new Label( 7 , rowIndex, model.getPrice(),getSummarySheetTextFormat()));
			ws.addCell(new Label( 8 , rowIndex, model.getSuccess(),getSummarySheetTextFormat()));
			ws.addCell(new Label( 9 , rowIndex, model.getMoney(),getSummarySheetTextFormat()));
		}

		ws.mergeCells(0, 7, 0, rowIndex);
		ws.mergeCells(1, 7, 1, rowIndex);
		ws.mergeCells(2, 7, 2, rowIndex);
		ws.mergeCells(3, 7, 3, rowIndex);
		ws.mergeCells(10, 7, 10, rowIndex);


		rowIndex+=3;
		for(int i=0;i<=10;i++){
			ws.addCell(new Label( i , rowIndex, "",getSummarySheetTextFormat(Border.BOTTOM)));
		}
		ws.addCell(new Label( 3 , rowIndex, "合计：",getSummarySheetTextFormat(Border.BOTTOM)));
		ws.addCell(new Label( 10 , rowIndex, sumMoney,getSummarySheetTextFormat(Border.BOTTOM)));

		rowIndex+=1;
		ws.addCell(new Label( 3 , rowIndex, "负责人盖章：",getSummarySheetTextFormat(Border.NONE)));

		rowIndex+=2;
		ws.addCell(new Label( 0 , rowIndex, "开户名称：",getSummarySheetTextFormat(Border.NONE)));

		rowIndex+=1;
		ws.addCell(new Label( 0 , rowIndex, "开户银行：",getSummarySheetTextFormat(Border.NONE)));

		rowIndex+=1;
		ws.addCell(new Label( 0 , rowIndex, "银行帐号：",getSummarySheetTextFormat(Border.NONE)));
		for(int i=1;i<=rowIndex;i++){
			ws.setRowView(i,rowHeight);
		}
	}

	public static void createEnterpriseI18nDetailSheet(WritableSheet ws, List<I18nBillDetailReportModel> models)throws WriteException{
		createEnterpriseI18nDetailBillSheetHead(ws);
		int rowHeight=500;
		for(int i=0;i<models.size();i++){
			ws.setRowView(i+1,rowHeight);
			I18nBillDetailReportModel model=models.get(i);
			Label account =  new Label( 0 , i+1 , model.getAccount(),getDetailSheetTableBodyFormat() );
			ws.addCell(account);
			Label countryCode =  new Label( 1 , i+1 , model.getCountryCode(),getDetailSheetTableBodyFormat() );
			ws.addCell(countryCode);
			Label countryName =  new Label( 2, i+1 , model.getCountryName(),getDetailSheetTableBodyFormat() );
			ws.addCell(countryName);
			Label sendDay =  new Label( 3 , i+1 , model.getSendDay(),getDetailSheetTableBodyFormat() );
			ws.addCell(sendDay);
			Label success =  new Label( 4 , i+1, model.getSuccess(),getDetailSheetTableBodyFormat() );
			ws.addCell(success);
			Label price =  new Label( 5 , i+1 , model.getPrice(),getDetailSheetTableBodyFormat() );
			ws.addCell(price);
			Label money =  new Label( 6 , i+1 , model.getMoney(),getDetailSheetTableBodyFormat() );
			ws.addCell(money);
		}
	}

	private static void createEnterpriseI18nDetailBillSheetHead(WritableSheet ws)throws WriteException{
		int columnWidth=20;
		int rowHeight=500;
		ws.setRowView(0,rowHeight);
		Label account =  new Label( 0 , 0 , " 发送账号",getDetailSheetTableHeadFormat() );
		ws.addCell(account);
		ws.setColumnView(0,columnWidth);
		Label countryCode =  new Label( 1 , 0 , " 国家代码",getDetailSheetTableHeadFormat() );
		ws.addCell(countryCode);
		ws.setColumnView(1,columnWidth);
		Label countryName =  new Label( 2, 0 , " 国家名称",getDetailSheetTableHeadFormat() );
		ws.addCell(countryName);
		ws.setColumnView(2,columnWidth);
		Label sendDay =  new Label( 3 , 0 , " 发送日期",getDetailSheetTableHeadFormat() );
		ws.addCell(sendDay);
		ws.setColumnView(3,columnWidth);
		Label success =  new Label( 4 , 0 , " 提交成功数",getDetailSheetTableHeadFormat() );
		ws.addCell(success);
		ws.setColumnView(4,columnWidth);
		Label price =  new Label( 5 , 0 , " 单价",getDetailSheetTableHeadFormat() );
		ws.addCell(price);
		ws.setColumnView(5,columnWidth);
		Label money =  new Label( 6 , 0 , " 结算金额",getDetailSheetTableHeadFormat() );
		ws.addCell(money);
		ws.setColumnView(6,columnWidth);
	}


	public static WritableCellFormat getDetailSheetTableHeadFormat()throws WriteException {
		WritableFont font = new WritableFont(WritableFont.createFont(FONT_NAME), 16,
				WritableFont.BOLD);
		WritableCellFormat format = new WritableCellFormat(font);
		format.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
		format.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
		format.setAlignment(Alignment.CENTRE); // 水平对齐
		return format;
	}

	public static WritableCellFormat getDetailSheetTableBodyFormat()throws WriteException{
		WritableFont font = new WritableFont(WritableFont.createFont(FONT_NAME), 16);
		WritableCellFormat format = new WritableCellFormat(font);
		format.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
		format.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
		format.setAlignment(Alignment.CENTRE);// 水平对齐
		return format;
	}

	public static WritableCellFormat getSummarySheetCompanyNameFormat()throws WriteException{
		WritableFont font = new WritableFont(WritableFont.createFont(FONT_NAME), 16,
				WritableFont.BOLD);
		WritableCellFormat format = new WritableCellFormat(font);
		format.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
		format.setAlignment(Alignment.CENTRE); // 水平对齐
		return format;
	}

	public static WritableCellFormat getSummarySheetTextFormat()throws WriteException{
		return getSummarySheetTextFormat(Border.ALL);
	}

	public static WritableCellFormat getSummarySheetTextFormat(Border b)throws WriteException{
		return getSummarySheetTextFormat(b,10);
	}

	public static WritableCellFormat getSummarySheetTextFormat(Border b,int fontSize)throws WriteException{
		return getSummarySheetTextFormat(b,fontSize,Alignment.CENTRE);
	}
	public static WritableCellFormat getSummarySheetTextFormat(Border b,int fontSize,Alignment a)throws WriteException{
		WritableFont font = new WritableFont(WritableFont.createFont(FONT_NAME), fontSize);
		WritableCellFormat format = new WritableCellFormat(font);
		format.setBorder(b, BorderLineStyle.THIN); // 线条
		format.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
		format.setAlignment(a);// 水平对齐
		return format;
	}
}
